package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.partner.PartnerCrowdsourceSubDto;
import com.info.zhiduoduo.repository.entity.partner.PartnerCrowdsourceSub;

import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2021-01-24
 */
public interface PartnerCrowdsourceSubMapper extends BaseMapper<PartnerCrowdsourceSub> {


	@Select(" select ps.*,pa.realname as partnerName,spa.realname as subPartnerName " +
			" from zdd_partner_crowdsource_sub ps " +
			" join zdd_partner_account pa on pa.partnerid = ps.partnerid " +
			" join zdd_partner_account spa on spa.partnerid = ps.sub_partnerid " +
			" where CONCAT(pa.realname,spa.realname) like '%${name}%' " +
			" and ps.deleted = 0 " +
			" order by ps.id desc ")
	List<PartnerCrowdsourceSubDto> findList(String name);

	@Select(" select ps.*,pa.realname as partnerName,spa.realname as subPartnerName " +
			" from zdd_partner_crowdsource_sub ps " +
			" join zdd_partner_account pa on pa.partnerid = ps.partnerid " +
			" join zdd_partner_account spa on spa.partnerid = ps.sub_partnerid " +
			" where ps.partnerid = #{partnerid} " +
			" and ps.deleted = 0 " +
			" and ps.state = 1 " +
			" order by ps.id desc ")
	List<PartnerCrowdsourceSubDto> findSubListByUpId(int partnerid);

	@Select(" select ps.*,pa.realname as partnerName,spa.realname as subPartnerName " +
			" from zdd_partner_crowdsource_sub ps " +
			" join zdd_partner_account pa on pa.partnerid = ps.partnerid " +
			" join zdd_partner_account spa on spa.partnerid = ps.sub_partnerid " +
			" where ps.partnerid = #{partnerid} " +
			" and ps.sub_partnerid = #{subPartnerid} " +
			" and ps.deleted = 0 ")
	PartnerCrowdsourceSubDto findByUpIdAndSubId(int partnerid, int subPartnerid);

	@Select(" select ps.*,pa.realname as partnerName,spa.realname as subPartnerName " +
			" from zdd_partner_crowdsource_sub ps " +
			" join zdd_partner_account pa on pa.partnerid = ps.partnerid " +
			" join zdd_partner_account spa on spa.partnerid = ps.sub_partnerid " +
			" where sub_partnerid = #{subPartnerid} " +
			" and ps.deleted = 0 " +
			" order by ps.id desc " +
			" limit 1 ")
	PartnerCrowdsourceSubDto findUpPartnerBySubId(int subPartnerid);


	@Select(" select ps.*,pa.realname as partnerName,spa.realname as subPartnerName " +
			" from zdd_partner_crowdsource_sub ps " +
			" join zdd_partner_account pa on pa.partnerid = ps.partnerid " +
			" join zdd_partner_account spa on spa.partnerid = ps.sub_partnerid " +
			" where ps.id = #{id} ")
	PartnerCrowdsourceSubDto findById(int id);

	@Select(" select COUNT(*) from zdd_partner_crowdsource_sub ps " +
			" where ps.partnerid = #{partnerid} " +
			" and ps.deleted = 0 ")
	int countSubPartner(int partnerid);
}
